package db.queries.core;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import core.exceptions.DBAccessException;
import core.objects.ObjectWithID;
import db.DBHandler;
import db.IDBAccess;
import db.dbTables.AbstractDBTable;
import db.dbTables.DbColumn;

public abstract class ObjectQueries<T extends ObjectWithID> implements IDBAccess<T> {
	
	protected abstract AbstractDBTable getTable();
	protected abstract DbColumn getIDColumn();
	protected abstract DbColumn getActualIDColumn();
	protected abstract void determineField(Object obj);
	
	protected abstract DBAccessException foreignKeyConstrainstViolation(String key, Throwable e);

	protected DBAccessException handleSQLException(SQLException e) {
		if (e.getMessage() != null) {
			String message = e.getMessage();
			if (message.contains("a foreign key constraint fails")) {
				int foreignKey = message.indexOf("FOREIGN KEY (");
				if (foreignKey > 0) {
					int end = message.indexOf(")", foreignKey);
					if (end > 0) {
						return foreignKeyConstrainstViolation(
								message.substring(foreignKey + ("FOREIGN KEY (").length() + 1, end - 1),
								e);
					}
				}
			}
		}
		return new DBAccessException("Unknown error has occured", e);
	}
	
	protected String orderBy() {
		return null;
	}
	
	@Override
	public boolean exists(String name) throws DBAccessException {
		//object-containing tables don't support exists by name method.
		throw new UnsupportedOperationException();
	}
	
	@Override
	public boolean exists(String name, int id, ObjectWithID obj) throws DBAccessException {
		//object-containing tables don't support exists by name method.
		throw new UnsupportedOperationException();
	}
	
	@Override
	public synchronized boolean exists(int idToSearch, int id, ObjectWithID obj) throws DBAccessException {
		determineField(obj);
		ResultSet rs = null;
		try {
			rs = DBHandler.executeQuery(
					QueriesEnvironment.selectQuery(
							getIDColumn().getName() + " = ? AND " + getActualIDColumn().getName() + " = ? ",
							new Object[] { idToSearch, id },
							getTable().getDbColumns().toArray(new DbColumn[0])));
	        if (!rs.first())
	        	return false;
	        return true;
		} catch (SQLException e) {
			throw handleSQLException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				throw handleSQLException(e);
			}
		}
	}

	@Override
	public synchronized List<T> select(int id) throws DBAccessException {
		determineField(null);
		return doSelect(id);
	}
	
	@Override
	public synchronized List<T> select(int id, ObjectWithID obj) throws DBAccessException {
		determineField(obj);
		return doSelect(id);
	}
	
	@SuppressWarnings("unchecked")
	private synchronized List<T> doSelect(int id) throws DBAccessException {
		try {
			String order = orderBy();
			return (List<T>)DBHandler.convertResultSetToBaseObjectList(
					DBHandler.executeQuery(
							QueriesEnvironment.selectQuery(
									getIDColumn().getName() + " = ? "
										+ (order != null ? order : ""),
									new Object[] { id },
									getTable().getDbColumns().toArray(new DbColumn[0]))),
					getTable());
		} catch (SQLException e) {
			throw handleSQLException(e);
		}
	}
	
	@Override
	public List<T> select(String name) throws DBAccessException {
		//only real cross tables can support this kind of select!
		//real cross tables are represented as objects within
		//objects, whether an objects representing tables (i.e. BookComment, etc)
		throw new UnsupportedOperationException();
	}

	@Override
	public List<T> selectContains(String name) throws DBAccessException {
		//these kinds of objects (Comments etc) don't support this
		//kind of select
		throw new UnsupportedOperationException();
	}

	@Override
	public List<T> selectContains(String name, boolean limitSearch) throws DBAccessException {
		//these kinds of objects (Comments etc) don't support this
		//kind of select
		throw new UnsupportedOperationException();
	}

	@Override
	public List<T> select(String name, ObjectWithID obj) throws DBAccessException {
		//these kinds of objects (Comments etc) don't support this
		//kind of select
		throw new UnsupportedOperationException();
	}

	@Override
	public List<T> selectContains(String name, ObjectWithID obj) throws DBAccessException {
		//these kinds of objects (Comments etc) don't support this
		//kind of select
		throw new UnsupportedOperationException();
	}

	@Override
	public List<T> selectContains(String name, ObjectWithID obj, boolean limitSearch) throws DBAccessException {
		//these kinds of objects (Comments etc) don't support this
		//kind of select
		throw new UnsupportedOperationException();
	}

	@Override
	public synchronized float selectAvg(int id) throws DBAccessException {
		determineField(0.0f); //mark wanted field as float
		ResultSet rs = null;
		try {
			rs = DBHandler.executeQuery(QueriesEnvironment.selectAvgQuery(
					getIDColumn(), getActualIDColumn(), id));
			if (!rs.first())
				return 0;
			return rs.getFloat(1);
		} catch (SQLException e) {
			throw handleSQLException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				throw handleSQLException(e);
			}
		}
	}

	@Override
	public synchronized int selectCount(int id, ObjectWithID obj) throws DBAccessException {
		determineField(obj);
		ResultSet rs = null;
		try {
			rs = DBHandler.executeQuery(QueriesEnvironment.selectCountQuery(getIDColumn(), id));
			if (!rs.first())
				return 0;
			return rs.getInt(1);
		} catch (SQLException e) {
			throw handleSQLException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				throw handleSQLException(e);
			}
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public synchronized List<T> selectAll() throws DBAccessException {
		determineField(null);
		try {
			String order = orderBy();
			return (List<T>) DBHandler.convertResultSetToBaseObjectList(
					DBHandler.executeQuery(
							QueriesEnvironment.selectQuery(
									(order != null ? order : null), null,
									getTable().getDbColumns().toArray(new DbColumn[0]))),
					getTable());
		} catch (SQLException e) {
			throw handleSQLException(e);
		}
	}

	@Override
	public synchronized List<? extends ObjectWithID> selectAll(int id, ObjectWithID obj) throws DBAccessException {
		//these kinds of objects (Comments etc) don't support this
		//kind of select
		throw new UnsupportedOperationException();
	}

	@Override
	public void insert(T obj) throws DBAccessException {
		determineField(null);
		ArrayList<T> objs = new ArrayList<T>();
		objs.add(obj);
		insertAll(objs);
	}
	
	@Override
	public void insert(T obj, ObjectWithID base) throws DBAccessException {
		//an object representing a cross table doesn't need this,
		//just use the insert in the containing class (i.e. book for
		//categories, authors and so on...)
		//use the regular insert method for a non-cross table
		//(i.e. BookComment, UserComment etc)
		throw new UnsupportedOperationException();
	}

	@Override
	public void insertAll(Collection<T> objs) throws DBAccessException {
		determineField(null);
		try {
 			if (objs != null && !objs.isEmpty()) {
				DBHandler.createAndExecuteBatchUpdate(
						getTable(), objs);
			}
		} catch (SQLException e) {
			throw handleSQLException(e);
		}
	}

	@Override
	public void insertAll(Collection<? extends ObjectWithID> objs,
			ObjectWithID filter) throws DBAccessException {
		throw new UnsupportedOperationException();
	}

	@Override
	public void insertAll(T obj, Collection<? extends ObjectWithID> objs) throws DBAccessException {
		//never use this for a non-cross object (UserComment, BookComment
		//etc - use regular insertAll).
		//for non-cross objects, use this method in the
		//containing class (i.e. book for categories, authors and so on).
		throw new UnsupportedOperationException();
	}
	
	@Override
	public void update(T obj) throws DBAccessException {
		ArrayList<T> objs = new ArrayList<T>();
		objs.add(obj);
		update(objs);
	}
	
	@Override
	public void update(Collection<T> objs) throws DBAccessException {
		try {
			DBHandler.executeUpdateBatch(QueriesEnvironment.doUpdate(objs, getTable()));
		} catch (SQLException e) {
			throw handleSQLException(e);
		}
	}
	
	@Override
	public void delete(T obj) throws DBAccessException {
		ArrayList<T> objs = new ArrayList<T>();
		objs.add(obj);
		delete(objs);
	}
	
	@Override
	public void delete(Collection<T> objs) throws DBAccessException {
		try {
			DBHandler.executeUpdateBatch(QueriesEnvironment.doDelete(objs, getTable()));
		} catch (SQLException e) {
			throw handleSQLException(e);
		}
	}
	
	@Override
	public void delete(T obj1, ObjectWithID obj2) throws DBAccessException {
		//never use this for a non-cross object (UserComment, BookComment etc)
		throw new UnsupportedOperationException();
	}
	
	@Override
	public void delete(T obj, Collection<? extends ObjectWithID> objs)
			throws DBAccessException {
		//never use this for a non-cross object (UserComment, BookComment etc)
		throw new UnsupportedOperationException();
	}
}
